from datetime import datetime
import os
import sqlite3
import sys

# 将 akshare 源代码路径添加到 sys.path
local_akshare_path = os.path.abspath("../../akshare")
sys.path.append(local_akshare_path)

import akshare as ak

now = datetime.now()

# Format date and time
month_str = now.strftime("%Y%m")
date_str = now.strftime("%d")
time_str = now.strftime("%H%M%S")

# Set up paths
base_path = "/media/devmon/seagate/trading/"
# month_path = base_path + month_str + "/"
# day_path = month_path + date_str + "/"
# os.makedirs(day_path, exist_ok=True)


# def getStockInfo():
#     # Get a list of files in the directory that start with "stock_"
#     files = [
#         f
#         for f in os.listdir(base_path)
#         if f.startswith("stock_") and f.endswith(".csv")
#     ]

#     # Sort files by date in descending order to get the latest file
#     files.sort(reverse=True)

#     # Load the latest file if it exists
#     if files:
#         csv_path = os.path.join(base_path, files[0])
#         stock_zh_a_spot_em_df = pd.read_csv(csv_path, encoding="utf-8")
#         print(f"Loaded file: {csv_path}")
#     else:
#         print("No file starting with 'stock_' found in the directory.")
#     return stock_zh_a_spot_em_df

conn = None


def getDb():
    global conn
    if conn is None:
        # 连接到 SQLite 数据库
        # conn = sqlite3.connect(base_path + "trade.db")
        conn = mysql.connector.connect(
            host="localhost",
            user="root",
            password="jf4SswsRthTTgan",
            database="trading",
        )
    cursor = conn.cursor()
    cursor.execute(
        """
        CREATE TABLE IF NOT EXISTS stock_info (
                    symbol VARCHAR(20) PRIMARY KEY, /* "代码" */
                    name VARCHAR(255), /* "名称" */
                    market_cap DECIMAL(20, 2), /* "总市值" */
                    circulating_cap DECIMAL(20, 2), /* "流通市值" */
                    pe_ratio_dynamic DECIMAL(10, 2), /* "市盈率-动态" */
                    pb_ratio DECIMAL(10, 2), /* "市净率" */
                    industry VARCHAR(255), /* "行业" */
                    listing_date DATE, /* "上市时间" */
                    total_shares DECIMAL(20, 2) /* "总股本" */
                );
    """
    )
    cursor.execute(
        """
        CREATE UNIQUE INDEX idx_symbol_unique ON stock_info (symbol);
        """
    )

    cursor.execute(
        """
        CREATE TABLE IF NOT EXISTS stock_daily (
            symbol VARCHAR(20),            /* 股票代码 */
            date DATE,                     /* 交易日 */
            open FLOAT,                    /* 开盘价 */
            high FLOAT,                    /* 最高价 */
            low FLOAT,                     /* 最低价 */
            close FLOAT,                   /* 收盘价 */
            volume FLOAT,                  /* 成交量; 注意单位: 股 */
            amount FLOAT,                  /* 成交额; 注意单位: 元 */
            outstanding_share FLOAT,       /* 流动股本; 注意单位: 股 */
            turnover FLOAT,                /* 换手率=成交量/流动股本 */
            amplitude FLOAT,               /* 振幅; 注意单位: % */
            price_change_rate FLOAT,       /* 涨跌幅; 注意单位: % */
            price_change_amount FLOAT,     /* 涨跌额; 注意单位: 元 */
            main_net_inflow_amount FLOAT,  /* 主力净流入-净额 */
            main_net_inflow_ratio FLOAT,   /* 主力净流入-净占比 */
            super_large_net_inflow_amount FLOAT, /* 超大单净流入-净额 */
            super_large_net_inflow_ratio FLOAT,  /* 超大单净流入-净占比 */
            large_net_inflow_amount FLOAT, /* 大单净流入-净额 */
            large_net_inflow_ratio FLOAT,  /* 大单净流入-净占比 */
            medium_net_inflow_amount FLOAT,/* 中单净流入-净额 */
            medium_net_inflow_ratio FLOAT, /* 中单净流入-净占比 */
            small_net_inflow_amount FLOAT, /* 小单净流入-净额 */
            small_net_inflow_ratio FLOAT,  /* 小单净流入-净占比 */
            buyer_institution_count FLOAT,  /* 买方机构数 */
            seller_institution_count FLOAT, /* 卖方机构数 */
            institution_buy_amount FLOAT,   /* 机构买入总额; 注意单位: 元 */
            institution_sell_amount FLOAT,  /* 机构卖出总额; 注意单位: 元 */
            institution_net_buy_amount FLOAT, /* 机构买入净额; 注意单位: 元 */
            total_market_turnover FLOAT,    /* 市场总成交额; 注意单位: 元 */
            institution_net_buy_ratio FLOAT, /* 机构净买额占总成交额比 */
            lhb_reason TEXT,                /* 上榜原因 */
            PRIMARY KEY (symbol, date)      /* 联合主键，确保 symbol 和 date 的唯一性 */
        );
    """
    )

    cursor.execute(
        """CREATE TABLE IF NOT EXISTS sector_daily (
                name VARCHAR(255),           /* "行业"板块名称 */
                type varchar(255),            /* "行业"板块  */
                date DATE,                       /* "日期" (格式 YYYYMMDD) */
                main_net_inflow FLOAT,          /* "主力净流入-净额" */
                main_net_ratio FLOAT,           /* "主力净流入-净占比 (%)" */
                huge_net_inflow FLOAT,          /* "超大单净流入-净额" */
                huge_net_ratio FLOAT,           /* "超大单净流入-净占比 (%)" */
                large_net_inflow FLOAT,         /* "大单净流入-净额" */
                large_net_ratio FLOAT,          /* "大单净流入-净占比 (%)" */
                medium_net_inflow FLOAT,        /* "中单净流入-净额" */
                medium_net_ratio FLOAT,         /* "中单净流入-净占比 (%)" */
                small_net_inflow FLOAT,         /* "小单净流入-净额" */
                small_net_ratio FLOAT           /* "小单净流入-净占比 (%)" */
            );"""
    )
    cursor.execute(
        """CREATE TABLE IF NOT EXISTS market_daily (
            sh_close FLOAT,                /* "上证-收盘价" */
            sh_rate FLOAT,                 /* "上证-涨跌幅" */
            sz_close FLOAT,                /* "深证-收盘价" */
            sz_rate FLOAT,                 /* "深证-涨跌幅" */
            date DATE,                     /* "日期" (格式 YYYYMMDD) */
            main_net_inflow FLOAT,         /* "主力净流入-净额" */
            main_net_ratio FLOAT,          /* "主力净流入-净占比 (%)" */
            huge_net_inflow FLOAT,         /* "超大单净流入-净额" */
            huge_net_ratio FLOAT,          /* "超大单净流入-净占比 (%)" */
            large_net_inflow FLOAT,        /* "大单净流入-净额" */
            large_net_ratio FLOAT,         /* "大单净流入-净占比 (%)" */
            medium_net_inflow FLOAT,       /* "中单净流入-净额" */
            medium_net_ratio FLOAT,        /* "中单净流入-净占比 (%)" */
            small_net_inflow FLOAT,        /* "小单净流入-净额" */
            small_net_ratio FLOAT          /* "小单净流入-净占比 (%)" */
        );"""
    )
    cursor.execute(
        """CREATE TABLE IF NOT EXISTS workday (
                trade_date DATE PRIMARY KEY
            );"""
    )
    conn.commit()
    return conn, cursor


trade_dates = list(ak.tool_trade_date_hist_sina()["trade_date"].values)


def get_trade_date(day=datetime.today(), offset=0):
    global trade_dates
    """
    获取上一个交易日或偏移后的交易日日期

    参数:
    - day: 基准日期 (默认为今天)
    - offset: 偏移量 (0 表示基准日期，正数向后，负数向前)

    返回:
    - 交易日日期
    """
    # 获取所有交易日数据并转换为日期列表
    today = day.date()

    # 检查今天是否是交易日，如果不是则找到最近的前一个交易日
    if today in trade_dates:
        base_trade_date = today
    else:
        # 找到今天以前最近的一个交易日
        base_trade_date = max(date for date in trade_dates if date < today)

    # 找到基于偏移量的最终交易日
    base_index = trade_dates.index(base_trade_date)
    return trade_dates[base_index + offset]


def get_market(symbol):
    """
    Determine the market based on the stock_info symbol.
    Returns:
        str: "sh" for Shanghai, "sz" for Shenzhen, "bj" for Beijing.
    """
    if symbol.startswith("60") or symbol.startswith("68"):
        return "sh"  # Shanghai stock_info Exchange (Main board or STAR Market)
    elif symbol.startswith("00") or symbol.startswith("30"):
        return "sz"  # Shenzhen stock_info Exchange (Main board or ChiNext)
    elif symbol.startswith("8") or symbol.startswith("43") or symbol.startswith("92"):
        return "bj"  # Beijing stock_info Exchange (including former NEEQ Select)
    else:
        print("Unknown or unsupported market:" + symbol)
        return "unknown"  # Unknown or unsupported market
